USE [Test_GCCS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetPrincipleEventIndicators]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetPrincipleEventIndicators]
GO

CREATE PROCEDURE [dbo].[spGetPrincipleEventIndicators]
AS
	DECLARE @PrincipleEventIndicators TABLE
	(
		ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
		EventIndicatorId UNIQUEIDENTIFIER NOT NULL,
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		PrincipleTemplateId INT NOT NULL,
		PrincipleGccsName NVARCHAR(50) NOT NULL,
		PrincipleGirName NVARCHAR(50) NOT NULL,
		PillarId INT NOT NULL,
		PillarName NVARCHAR(100) NOT NULL,

		PrincipleAssessment INT NOT NULL,

		BaseEventIndicatorId UNIQUEIDENTIFIER NULL,
		IssueType NVARCHAR(250) NOT NULL,
		EventIndicatorAssessment INT NOT NULL,
		EventSummary NVARCHAR(1500) NOT NULL,

		ImpactSummary NVARCHAR(300) NOT NULL,
		ImpactComment1 NVARCHAR(1200) NULL,
		ImpactComment2 NVARCHAR(1200) NULL,
		ImpactComment3 NVARCHAR(1200) NULL,
		ImpactComment4 NVARCHAR(1200) NULL,
		ImpactComment5 NVARCHAR(1200) NULL,

		ManagementSummary NVARCHAR(300) NOT NULL,
		ManagementComment1 NVARCHAR(1200) NULL,
		ManagementComment2 NVARCHAR(1200) NULL,
		ManagementComment3 NVARCHAR(1200) NULL,
		ManagementComment4 NVARCHAR(1200) NULL,
		ManagementComment5 NVARCHAR(1200) NULL
	)
	INSERT INTO @PrincipleEventIndicators
	SELECT	dbo.GccsEventIndicators.Id AS EventIndicatorId,
			dbo.GccsCompanyProfiles.CompanyId,
			dbo.GccsPrincipleTemplates.Id AS PrincipleTemplateId, 
			dbo.GccsPrincipleTemplates.GccsName AS PrincipleGccsName, 
			dbo.GccsPrincipleTemplates.GirName AS PrincipleGirName,
            GccsPillarTemplates.Id AS PillarId, 
			GccsPillarTemplates.Name AS PillarName, 
			Principles.Assessment AS PrincipleAssessment, 

			dbo.GccsEventIndicators.RpBaseEventIndicatorId AS BaseEventIndicatorId,
			dbo.GccsEventIndicators.IssueType,
			dbo.GccsEventIndicators.Assessment AS EventIndicatorAssessment, 
			dbo.GccsEventIndicators.EventSummary, 
            
			dbo.GccsEventIndicators.ImpactIntroductionSentence AS ImpactSummary, 
			NULL AS ImpactComment1,
			NULL AS ImpactComment2,
			NULL AS ImpactComment3,
			NULL AS ImpactComment4,
			NULL AS ImpactComment5,
			
			dbo.GccsEventIndicators.ManagementIntroductionSentence AS ManagementSummary,
			NULL AS ManagementComment1,
			NULL AS ManagementComment2,
			NULL AS ManagementComment3,
			NULL AS ManagementComment4,
			NULL AS ManagementComment5
	FROM    dbo.GccsPrinciples AS Principles INNER JOIN
            dbo.GccsPillars ON Principles.PillarId = dbo.GccsPillars.Id INNER JOIN
            dbo.GccsCompanyProfiles ON dbo.GccsPillars.CompanyProfilerId = dbo.GccsCompanyProfiles.Id INNER JOIN
            dbo.GccsPrincipleTemplates ON Principles.TemplateId = dbo.GccsPrincipleTemplates.Id INNER JOIN
            dbo.GccsEventIndicators ON Principles.Id = dbo.GccsEventIndicators.PrincipleId INNER JOIN
            dbo.GccsPillarTemplates ON dbo.GccsPillars.TemplateId = dbo.GccsPillarTemplates.Id
	WHERE  (dbo.GccsCompanyProfiles.OverallCompanyAssessment = 1 OR dbo.GccsCompanyProfiles.OverallCompanyAssessment = 2) -- 1 = Watchlist or 2 = NonCompliant
	   AND (dbo.GccsCompanyProfiles.GccsWorkflowStatus = 3) -- Final
	   AND (dbo.GccsEventIndicators.Assessment = 1 OR dbo.GccsEventIndicators.Assessment = 2)

	DECLARE @ID int
	DECLARE @Count int

	SELECT @ID = MIN(ID) FROM @PrincipleEventIndicators
	SELECT @Count = MAX(ID) FROM @PrincipleEventIndicators

	DECLARE @EventIndicatorId UNIQUEIDENTIFIER
	DECLARE @ImpactComments TABLE
	(
		EventIndicatorId UNIQUEIDENTIFIER NOT NULL,
		ImpactComment1 NVARCHAR(1200) NULL,
		ImpactComment2 NVARCHAR(1200) NULL,
		ImpactComment3 NVARCHAR(1200) NULL,
		ImpactComment4 NVARCHAR(1200) NULL,
		ImpactComment5 NVARCHAR(1200) NULL
	)
	DECLARE @ManagementComments TABLE
	(
		EventIndicatorId UNIQUEIDENTIFIER NOT NULL,
		ManagementComment1 NVARCHAR(1200) NULL,
		ManagementComment2 NVARCHAR(1200) NULL,
		ManagementComment3 NVARCHAR(1200) NULL,
		ManagementComment4 NVARCHAR(1200) NULL,
		ManagementComment5 NVARCHAR(1200) NULL
	)

	WHILE @ID <= @Count BEGIN

		SELECT @EventIndicatorId = EventIndicatorId
		FROM @PrincipleEventIndicators
		WHERE ID = @ID

		INSERT INTO @ImpactComments
		SELECT * FROM dbo.fnGetEventIndicatorImpactComments(@EventIndicatorId)

		INSERT INTO @ManagementComments
		SELECT * FROM dbo.fnGetEventIndicatorManagementComments(@EventIndicatorId)

		SET @ID = @ID + 1
	END

	UPDATE @PrincipleEventIndicators
		SET ImpactComment1 = ImpactComments.ImpactComment1,
			ImpactComment2 = ImpactComments.ImpactComment2,
			ImpactComment3 = ImpactComments.ImpactComment3,
			ImpactComment4 = ImpactComments.ImpactComment4,
			ImpactComment5 = ImpactComments.ImpactComment5,
			ManagementComment1 = ManagementComments.ManagementComment1,
			ManagementComment2 = ManagementComments.ManagementComment2,
			ManagementComment3 = ManagementComments.ManagementComment3,
			ManagementComment4 = ManagementComments.ManagementComment4,
			ManagementComment5 = ManagementComments.ManagementComment5
	FROM @PrincipleEventIndicators AS PrincipleEventIndicators 
	INNER JOIN @ImpactComments AS ImpactComments ON PrincipleEventIndicators.EventIndicatorId = ImpactComments.EventIndicatorId
	INNER JOIN @ManagementComments AS ManagementComments ON PrincipleEventIndicators.EventIndicatorId = ManagementComments.EventIndicatorId

	SELECT CompanyId, PrincipleTemplateId, PrincipleGccsName, PrincipleGirName, PillarId, PillarName, PrincipleAssessment, BaseEventIndicatorId, IssueType, EventIndicatorAssessment, EventSummary,  ImpactSummary, ImpactComment1, ImpactComment2, ImpactComment3, ImpactComment4, ImpactComment5, ManagementSummary, ManagementComment1, ManagementComment2, ManagementComment3, ManagementComment4, ManagementComment5
	FROM @PrincipleEventIndicators

RETURN 0
GO


